package util

import entity.MyContract
import entity.PbcEbookEntity
import java.io.File
import java.sql.Connection
import java.sql.DriverManager

class MyDbHelper private constructor(){

    private var conn: Connection = buildConn()

    private fun buildConn(): Connection {
        if (!File(MyContract.PBC_E_BOOK_DB_DIR).exists()) {
            File(MyContract.PBC_E_BOOK_DB_DIR).mkdirs()
        }

        return DriverManager.getConnection("jdbc:sqlite:${MyContract.PBC_E_BOOK_DB_DIR + MyContract.PBC_E_BOOK_DB_NAME}")
    }

    companion object {
        val instance: MyDbHelper by lazy { MyDbHelper() }
    }


    /**
     * Create the pbc ebooks table.
     */
    fun onCreateTable(): Int {
        try {
            if (conn.isClosed) conn = buildConn()

            val statement = conn.createStatement()
            statement.queryTimeout = 30

            val sql = "CREATE TABLE IF NOT EXISTS ${MyContract.PbcEbookTable.TABLE_NAME}(" +
                    "${MyContract.PbcEbookTable.ID} INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "${MyContract.PbcEbookTable.NAME} TEXT NOT NULL," +
                    "${MyContract.PbcEbookTable.PATH} TEXT NOT NULL," +
                    "${MyContract.PbcEbookTable.ISDIR} BOOLEAN NOT NULL," +
                    "${MyContract.PbcEbookTable.CONTENT} TEXT)"
            val resultCode = statement.executeUpdate(sql)

            statement.close()
            return resultCode
        } finally {
            onClose()
        }
    }

    /**
     * Create the pbc ebooks table.
     */
    fun onDropTable(): Int {
        if (conn.isClosed) conn = buildConn()

        val statement = conn.createStatement()
        statement.queryTimeout = 30

        val sql = "DROP TABLE IF EXISTS ${MyContract.PbcEbookTable.TABLE_NAME}"
        val resultCode = statement.executeUpdate(sql)

        statement.close()
        return resultCode
    }

    fun onClose() {
        if (!conn.isClosed) conn.close()
    }

    fun insertEbooks(pbcEbooks: List<PbcEbookEntity>): List<PbcEbookEntity> {
        try {
            val resultList = mutableListOf<PbcEbookEntity>()

            if (conn.isClosed) conn = buildConn()
            conn.autoCommit = false

            pbcEbooks.forEach {
                val insertedId = insertEbook(it)
                resultList.add(it.copy(id = insertedId))
            }

            conn.commit()
            conn.autoCommit = true

            return resultList
        } finally {
            onClose()
        }
    }

    fun getInsertedId(): Int{
        try {
            if (conn.isClosed) conn = buildConn()
            val statement = conn.createStatement()
            statement.queryTimeout = 30

            val sql = "SELECT last_insert_rowid() from ${MyContract.PbcEbookTable.TABLE_NAME}"
            val resultSet = statement.executeQuery(sql)
            resultSet.first()
            val result = resultSet.getInt(0)
            statement.close()

            return result
        } finally {
            onClose()
        }
    }

    fun insertEbook(pbcEbookEntity: PbcEbookEntity): Int {

        if (conn.isClosed) conn = buildConn()

        val statement = conn.createStatement()
        statement.queryTimeout = 30

        val sql = "INSERT OR IGNORE INTO ${MyContract.PbcEbookTable.TABLE_NAME} (" +
                "${MyContract.PbcEbookTable.NAME}, " +
                "${MyContract.PbcEbookTable.PATH}," +
                "${MyContract.PbcEbookTable.ISDIR}," +
                "${MyContract.PbcEbookTable.CONTENT}) VALUES (" +
                "'${pbcEbookEntity.name}'," +
                "'${pbcEbookEntity.path}'," +
                "${if(pbcEbookEntity.is_dir) 1 else 0}," +
                "'${pbcEbookEntity.content}')"

        var resultCode = statement.executeUpdate(sql)
        if (resultCode >= 0) {
            val idRS = statement.executeQuery("select last_insert_rowid() newid")
            if (idRS.next()) {
                resultCode = idRS.getInt("newid")
            }
        }
        statement.close()

        return resultCode

    }

    fun updateEbookById(id: Int, newEbook: PbcEbookEntity): Int {
        try {
            if (conn.isClosed) conn = buildConn()

            val statement = conn.createStatement()

            val sql = "UPDATE ${MyContract.PbcEbookTable.TABLE_NAME} " +
                    "SET ${MyContract.PbcEbookTable.NAME} = '${newEbook.name}', " +
                    "${MyContract.PbcEbookTable.PATH} = '${newEbook.path}', " +
                    "${MyContract.PbcEbookTable.CONTENT} = '${newEbook.content}' " +
                    "WHERE ${MyContract.PbcEbookTable.ID} = $id"

            return statement.executeUpdate(sql)
        } finally {
            onClose()
        }
    }

    fun clearAllEbooks(): Int {
        if (conn.isClosed) conn = buildConn()

        val statement = conn.createStatement()
        statement.queryTimeout = 30

        val sql = "DELETE FROM ${MyContract.PbcEbookTable.TABLE_NAME}"

        val resultCode = statement.executeUpdate(sql)
        statement.close()

        return resultCode
    }

    fun deleteEbookById(id: Int): Int {
        if (conn.isClosed) conn = buildConn()

        val statement = conn.createStatement()
        statement.queryTimeout = 30

        val sql = "DELETE FROM ${MyContract.PbcEbookTable.TABLE_NAME} WHERE ${MyContract.PbcEbookTable.ID} = $id"

        val resultCode = statement.executeUpdate(sql)
        statement.close()

        return resultCode
    }

    fun selectAllEbooks(): List<PbcEbookEntity> {
        try {
            if (conn.isClosed) conn = buildConn()

            val pbcEbooks = mutableListOf<PbcEbookEntity>()

            val statement = conn.createStatement()
            statement.queryTimeout = 30
            val sql = "SELECT * FROM ${MyContract.PbcEbookTable.TABLE_NAME} ORDER BY ${MyContract.PbcEbookTable.ID}"
            val resultSet = statement.executeQuery(sql)
            while (resultSet.next()) {
                val id = resultSet.getInt(MyContract.PbcEbookTable.ID)
                val name = resultSet.getString(MyContract.PbcEbookTable.NAME)
                val path = resultSet.getString(MyContract.PbcEbookTable.PATH)
                val isDir = resultSet.getBoolean(MyContract.PbcEbookTable.ISDIR)
                val content = resultSet.getString(MyContract.PbcEbookTable.CONTENT)
                pbcEbooks.add(PbcEbookEntity(id, name, path, isDir, content))
            }
            statement.close()

            return pbcEbooks
        } finally {
          onClose()
        }
    }

    fun selectContentListByPath(removePath: String): List<String> {
        try {


            val resultList = mutableListOf<String>()

            if (conn.isClosed) conn = buildConn()
            val statement = conn.createStatement()
            statement.queryTimeout = 30

            val sql = "SELECT ${MyContract.PbcEbookTable.CONTENT} FROM ${MyContract.PbcEbookTable.TABLE_NAME} WHERE ${MyContract.PbcEbookTable.PATH} like '$removePath%'"

            val resultSet = statement.executeQuery(sql)

            while (resultSet.next()) {
                val content = resultSet.getString(MyContract.PbcEbookTable.CONTENT)
                resultList.add(content)
            }

            return resultList
        } finally {
            onClose()
        }
    }

    fun selectPbcEbooksByFatherPath(fatherPath: String): List<PbcEbookEntity> {
        try {

            val resultList = mutableListOf<PbcEbookEntity>()

            if (conn.isClosed) conn = buildConn()
            val statement = conn.createStatement()
            statement.queryTimeout = 30

            val sql = "SELECT * FROM ${MyContract.PbcEbookTable.TABLE_NAME} WHERE ${MyContract.PbcEbookTable.PATH} like '$fatherPath/%'"
            val resultSet = statement.executeQuery(sql)

            while (resultSet.next()) {
                val id = resultSet.getInt(MyContract.PbcEbookTable.ID)
                val name = resultSet.getString(MyContract.PbcEbookTable.NAME)
                val path = resultSet.getString(MyContract.PbcEbookTable.PATH)
                val isDir = resultSet.getBoolean(MyContract.PbcEbookTable.ISDIR)
                val content = resultSet.getString(MyContract.PbcEbookTable.CONTENT)

                resultList.add(PbcEbookEntity(id, name, path, isDir, content))
            }

            return resultList
        }finally {
            onClose()
        }
    }

    fun deleteEbookByPath(removePath: String): Int{
        if (conn.isClosed) conn = buildConn()

        val statement = conn.createStatement()
        statement.queryTimeout = 30

        val sql = "DELETE FROM ${MyContract.PbcEbookTable.TABLE_NAME} WHERE ${MyContract.PbcEbookTable.PATH} like '$removePath%'"

        val resultCode = statement.executeUpdate(sql)
        statement.close()

        return resultCode
    }

    fun selectEbooksByKeyWordsInName(searchText: String): List<PbcEbookEntity> {
        try {
            if (conn.isClosed) conn = buildConn()

            val pbcEbooks = mutableListOf<PbcEbookEntity>()

            val statement = conn.createStatement()
            statement.queryTimeout = 30


            val sql = "SELECT * FROM ${MyContract.PbcEbookTable.TABLE_NAME} " +
                    "WHERE NOT ${MyContract.PbcEbookTable.ISDIR} " +
                    "AND ${MyContract.PbcEbookTable.NAME} like '%$searchText%'"

            val resultSet = statement.executeQuery(sql)
            while (resultSet.next()) {
                val id = resultSet.getInt(MyContract.PbcEbookTable.ID)
                val name = resultSet.getString(MyContract.PbcEbookTable.NAME)
                val path = resultSet.getString(MyContract.PbcEbookTable.PATH)
                val isDir = resultSet.getBoolean(MyContract.PbcEbookTable.ISDIR)
                val content = resultSet.getString(MyContract.PbcEbookTable.CONTENT)
                pbcEbooks.add(PbcEbookEntity(id, name, path, isDir, content))
            }
            statement.close()

            return pbcEbooks
        }finally {
            onClose()
        }

    }

    fun movePbcEbookUp(ebook: PbcEbookEntity) : Int {
        if (conn.isClosed) conn = buildConn()

        val statement = conn.createStatement()

        val sqlSelectUp = "SELECT * FROM ${MyContract.PbcEbookTable.TABLE_NAME} " +
                "WHERE ${MyContract.PbcEbookTable.ID} = ${ebook.id - 1}"
        val resultSet = statement.executeQuery(sqlSelectUp)
        if (resultSet.next()) {
            val id = resultSet.getInt(MyContract.PbcEbookTable.ID)
            val name = resultSet.getString(MyContract.PbcEbookTable.NAME)
            val path = resultSet.getString(MyContract.PbcEbookTable.PATH)
            val isDir = resultSet.getBoolean(MyContract.PbcEbookTable.ISDIR)
            val content = resultSet.getString(MyContract.PbcEbookTable.CONTENT)
            val upEbook = PbcEbookEntity(id, name, path, isDir, content)

            if (upEbook.path.split("/").size == ebook.path.split("/").size) {
                if (upEbook.path.substring(0, upEbook.path.lastIndexOf("/")) ==
                        ebook.path.substring(0, upEbook.path.lastIndexOf("/"))) {

                    val sqlChangeUp = "UPDATE ${MyContract.PbcEbookTable.TABLE_NAME} " +
                            "SET ${MyContract.PbcEbookTable.NAME} = '${ebook.name}', " +
                            "${MyContract.PbcEbookTable.PATH} = '${ebook.path}', " +
                            "${MyContract.PbcEbookTable.ISDIR} = '${if(ebook.is_dir) 1 else 0}', " +
                            "${MyContract.PbcEbookTable.CONTENT} = '${ebook.content}' " +
                            "WHERE ${MyContract.PbcEbookTable.ID} = ${upEbook.id}"
                    val sqlChangeCur = "UPDATE ${MyContract.PbcEbookTable.TABLE_NAME} " +
                            "SET ${MyContract.PbcEbookTable.NAME} = '${upEbook.name}', " +
                            "${MyContract.PbcEbookTable.PATH} = '${upEbook.path}', " +
                            "${MyContract.PbcEbookTable.ISDIR} = '${if(upEbook.is_dir) 1 else 0}', " +
                            "${MyContract.PbcEbookTable.CONTENT} = '${upEbook.content}' " +
                            "WHERE ${MyContract.PbcEbookTable.ID} = ${ebook.id}"

                    val resultCode1 = statement.executeUpdate(sqlChangeUp)
                    val resultCode2 = statement.executeUpdate(sqlChangeCur)

                    return resultCode1 + resultCode2
                }
            }
        }

        return -1
    }

    fun movePbcEbookDown(ebook: PbcEbookEntity) : Int {
        if (conn.isClosed) conn = buildConn()

        val statement = conn.createStatement()

        val sqlSelectDown = "SELECT * FROM ${MyContract.PbcEbookTable.TABLE_NAME} " +
                "WHERE ${MyContract.PbcEbookTable.ID} = ${ebook.id + 1}"
        val resultSet = statement.executeQuery(sqlSelectDown)
        if (resultSet.next()) {
            val id = resultSet.getInt(MyContract.PbcEbookTable.ID)
            val name = resultSet.getString(MyContract.PbcEbookTable.NAME)
            val path = resultSet.getString(MyContract.PbcEbookTable.PATH)
            val isDir = resultSet.getBoolean(MyContract.PbcEbookTable.ISDIR)
            val content = resultSet.getString(MyContract.PbcEbookTable.CONTENT)
            val downEbook = PbcEbookEntity(id, name, path, isDir, content)

            if (downEbook.path.split("/").size == ebook.path.split("/").size) {
                if (downEbook.path.substring(0, downEbook.path.lastIndexOf("/")) ==
                        ebook.path.substring(0, downEbook.path.lastIndexOf("/"))) {

                    val sqlChangeUp = "UPDATE ${MyContract.PbcEbookTable.TABLE_NAME} " +
                            "SET ${MyContract.PbcEbookTable.NAME} = '${ebook.name}', " +
                            "${MyContract.PbcEbookTable.PATH} = '${ebook.path}', " +
                            "${MyContract.PbcEbookTable.ISDIR} = '${if(ebook.is_dir) 1 else 0}', " +
                            "${MyContract.PbcEbookTable.CONTENT} = '${ebook.content}' " +
                            "WHERE ${MyContract.PbcEbookTable.ID} = ${downEbook.id}"
                    val sqlChangeCur = "UPDATE ${MyContract.PbcEbookTable.TABLE_NAME} " +
                            "SET ${MyContract.PbcEbookTable.NAME} = '${downEbook.name}', " +
                            "${MyContract.PbcEbookTable.PATH} = '${downEbook.path}', " +
                            "${MyContract.PbcEbookTable.ISDIR} = '${if(downEbook.is_dir) 1 else 0}', " +
                            "${MyContract.PbcEbookTable.CONTENT} = '${downEbook.content}' " +
                            "WHERE ${MyContract.PbcEbookTable.ID} = ${ebook.id}"

                    val resultCode1 = statement.executeUpdate(sqlChangeUp)
                    val resultCode2 = statement.executeUpdate(sqlChangeCur)

                    return resultCode1 + resultCode2
                }
            }
        }
        return -1
    }

    fun exchangeTowEbooks(selectedItem: PbcEbookEntity, anotherItem: PbcEbookEntity) : Int{
        try {
            if (conn.isClosed) conn = buildConn()

            val statement = conn.createStatement()

            if (selectedItem.path.split("/").size == anotherItem.path.split("/").size) {
                if (selectedItem.path.substring(0, selectedItem.path.lastIndexOf("/")) ==
                        anotherItem.path.substring(0, anotherItem.path.lastIndexOf("/"))) {

                    val sqlChangeAnother = "UPDATE ${MyContract.PbcEbookTable.TABLE_NAME} " +
                            "SET ${MyContract.PbcEbookTable.NAME} = '${selectedItem.name}', " +
                            "${MyContract.PbcEbookTable.PATH} = '${selectedItem.path}', " +
                            "${MyContract.PbcEbookTable.ISDIR} = '${if(selectedItem.is_dir) 1 else 0}', " +
                            "${MyContract.PbcEbookTable.CONTENT} = '${selectedItem.content}' " +
                            "WHERE ${MyContract.PbcEbookTable.ID} = ${anotherItem.id};"
                    val sqlChangeCur = "UPDATE ${MyContract.PbcEbookTable.TABLE_NAME} " +
                            "SET ${MyContract.PbcEbookTable.NAME} = '${anotherItem.name}', " +
                            "${MyContract.PbcEbookTable.PATH} = '${anotherItem.path}', " +
                            "${MyContract.PbcEbookTable.ISDIR} = '${if(anotherItem.is_dir) 1 else 0}', " +
                            "${MyContract.PbcEbookTable.CONTENT} = '${anotherItem.content}' " +
                            "WHERE ${MyContract.PbcEbookTable.ID} = ${selectedItem.id};"

                    val sql = sqlChangeAnother + sqlChangeCur
                    return statement.executeUpdate(sql)
                }
            }

            return -1
        }finally {
            onClose()
        }
    }

}